﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
namespace DAO
{
    public class NhanVienDAO
    {
        public List<NhanVienDTO> LayThongTinNhanVien() // lấy thông Nhân Viên
        {
            string sql = "SELECT * FROM NHANVIEN ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> ds = new List<NhanVienDTO>();
            while (reader.Read())
            {
                NhanVienDTO khDTO = new NhanVienDTO();
                khDTO.MaNV = reader.GetString(0);
                khDTO.TenNV = reader.GetString(1);
                khDTO.MatKhau = reader.GetString(2);
                khDTO.GTinh = reader.GetString(3);
                khDTO.DChi = reader.GetString(4);
                khDTO.DThoai = reader.GetString(5);
                ds.Add(khDTO);
            }
            conn.Close();
            return ds;
        }

        public List<NhanVienDTO> LayThongTinNhanVien(string MaNV) // lấy thông Nhân Viên theo maNV
        {
            string sql = "SELECT * FROM NHANVIEN WHERE MaNV = '"+MaNV+"'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<NhanVienDTO> ds = new List<NhanVienDTO>();
            while (reader.Read())
            {
                NhanVienDTO khDTO = new NhanVienDTO();
                khDTO.MaNV = reader.GetString(0);
                khDTO.TenNV = reader.GetString(1);
                khDTO.MatKhau = reader.GetString(2);
                khDTO.GTinh = reader.GetString(3);
                khDTO.DChi = reader.GetString(4);
                khDTO.DThoai = reader.GetString(5);
                ds.Add(khDTO);
            }
            conn.Close();
            return ds;
        }

        public string LayMaNVLonNhat() // LayMaNV lon nhat
        {
            string sql = "SELECT TOP  1, MaNV FROM NHANVIEN WHERE MaNV <> 'quanly'  ORDER BY MaNV DESC  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                
                nvDTO.MaNVLN = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.MaNVLN;
        }

        public string NextID(string lastID, string prefixID)
        {
            if (lastID == "")
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }

        public int ThemNhanVien(NhanVienDTO nvDTO)
        { 
            //tao ket noi csdl
            OleDbConnection conn = DataProvider.ConnectDB();
            //viet sql
            string sql= "INSERT INTO NhanVien(MaNV,TenNV,MatKhau,GTinh,DChi,DThoai) ";
            sql = sql + " VALUES(@MaNV,@TenNV,@MatKhau,@GTinh,@DChi,@DThoai) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaNV",OleDbType.VarChar);
            para.Value = nvDTO.MaNV;
            para = cmd.Parameters.Add("@TenNV", OleDbType.VarChar);
            para.Value = nvDTO.TenNV;
            para = cmd.Parameters.Add("@MatKhau", OleDbType.VarChar);
            para.Value = nvDTO.MatKhau;
            para = cmd.Parameters.Add("@GTinh", OleDbType.VarChar);
            para.Value = nvDTO.GTinh;
            para = cmd.Parameters.Add("@DChi", OleDbType.VarChar);
            para.Value = nvDTO.DChi;
            para = cmd.Parameters.Add("@DThoai", OleDbType.VarChar);
            para.Value = nvDTO.DThoai;

            int kq = cmd.ExecuteNonQuery();

            conn.Close();
            if (kq >= 0)
                return 1;
            return 0;

            

        }

        public string TaoMaNVMoi()
        {
            string LastID = LayMaNVLonNhat();
            string NewID = NextID(LastID, "NV");
            return NewID;
        }

        public int KTDangNhap(NhanVienDTO nvDTO)
        {
     
            string sql = "SELECT MaNV FROM NHANVIEN WHERE MaNV = '" + nvDTO.MaNV +  "' AND MatKhau = '" + nvDTO.MatKhau +  "'" ;
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nv = new NhanVienDTO();
            while (reader.Read())
            {

               nv.MaNV = reader.GetString(0);
            }
            conn.Close();
            if (nv.MaNV != null)
                return 1;
            return 0;

 
        }

        public string LayMaNVDN(NhanVienDTO nvDTO)
        {

            string sql = "SELECT MaNV FROM NHANVIEN WHERE MaNV = '" + nvDTO.MaNV + "' AND MatKhau = '" + nvDTO.MatKhau + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nv = new NhanVienDTO();
            while (reader.Read())
            {

                nv.MaNV = reader.GetString(0);
            }
            conn.Close();
            return nv.MaNV;


        }

        public string LayDiaChiNhanVien(string MaNV)
        {
            string sql = "SELECT DChi FROM NHANVIEN WHERE MaNV = '" + MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                nvDTO.DChi = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.DChi;
        }

        public string LayDienThoaiNhanVien(string MaNV)
        {
            string sql = "SELECT DThoai FROM NHANVIEN WHERE MaNV = '" + MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                nvDTO.DThoai = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.DThoai;
        }

        public string LayTenNhanVien(string MaNV)
        {
            string sql = "SELECT TenNV FROM NHANVIEN WHERE MaNV = '" + MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                nvDTO.TenNV = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.TenNV;
        }

        public int XoaNV(NhanVienDTO nvDTO)
        {

            string sql = "DELETE FROM NHANVIEN WHERE MaNV = '" + nvDTO.MaNV + "'" ;
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nv = new NhanVienDTO();
            while (reader.Read())
            {

                nv.MaNV = reader.GetString(0);
            }
            conn.Close();
            if (nv.MaNV == null)
                return 1;
            return 0;


        }

        public string LayMatKhauNhanVien(string MaNV)
        {
            string sql = "SELECT MatKhau FROM NHANVIEN WHERE MaNV = '" + MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                nvDTO.MatKhau = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.MatKhau;
        }

        public string LayGioiTinhNhanVien(string MaNV)
        {
            string sql = "SELECT GTinh FROM NHANVIEN WHERE MaNV = '" + MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            NhanVienDTO nvDTO = new NhanVienDTO();
            while (reader.Read())
            {
                nvDTO.GTinh = reader.GetString(0);
            }
            conn.Close();
            return nvDTO.GTinh;
        }


        public int SuaNV(NhanVienDTO nvDTO)
        {

            string sql = "UPDATE NHANVIEN SET TenNV = '"+ nvDTO.TenNV + "',MatKhau = '" + nvDTO.MatKhau+"',GTinh = '" + nvDTO.GTinh;
            sql = sql + "',DChi= '"+ nvDTO.DChi +"',DThoai = '"+ nvDTO.DThoai + "' WHERE MaNV ='"+ nvDTO.MaNV + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int kq = cmd.ExecuteNonQuery();

            conn.Close();
            if (kq >= 0)
                return 1;
            return 0;



        }

    }
}
